package com.myconnectionpool;


import java.sql.*;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * @author hfxiang
 * @class_name Dao
 * @describe
 * @create_date 2022/3/6 13:35
 */
public class Dao {
    private static Dao dao = new Dao();
    final String DRIVER = "com.mysql.jdbc.Driver";
    final String URL = "jdbc:mysql://localhost:3306/db_spider?characterEncoding=utf-8";
    final String USERNAME = "";
    final String PASSWORD = "";



    private Dao(){
    }
    static public Dao getDao(){
        return dao;
    }

    public void close() {

    }

    public void insertNote(String noteId,String modifyDate,String createDate,String content,String subject){
        try{
            //Class.forName(DRIVER);
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 执行查询
            System.out.println(" 插入数据："+noteId);

            String sql;
            sql = "INSERT INTO xiaomi_note (note_id, modify_date, create_date, content, subject) VALUE(?,?,?,?,?)";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,noteId);
            preparedStatement.setString(2,modifyDate);
            preparedStatement.setString(3,createDate);
            preparedStatement.setString(4,content);
            preparedStatement.setString(5,subject);

            preparedStatement.executeUpdate();
            preparedStatement.close();
            conn.close();

        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }

    }

    public void insertNoteId(String id){
        try{
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 执行查询
            System.out.println(" 插入数据："+id);

            String sql;
            sql = "INSERT INTO xiaomi_note_id (note_id) VALUE(?)";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,id);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            conn.close();

        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }

    }

    public List<Object> queryAll(String tableName){
        List<Object> xiaomiNotes =new ArrayList();
        try{
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            Statement stmt = conn.createStatement();

            // 执行查询
            System.out.println(" 实例化Statement对象...");
            String sql;
            sql = "SELECT * FROM "+tableName;
            ResultSet rs = stmt.executeQuery(sql);
            // 展开结果集数据库
            while(rs.next()){
                // 通过字段检索
                int id  = rs.getInt("id");
                String name = rs.getString("temp");
               // xiaomiNotes.add(XiaomiNote.builder().id(id).temp(name).build());
            }
            rs.close();

        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
        return xiaomiNotes;
    }
    public Set<String> getAllXiaoMiNoteId(){
        Set<String> xiaomiNoteIds =new HashSet();
        try{
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            Statement stmt = conn.createStatement();

            // 执行查询
            System.out.println(" 实例化Statement对象...");
            String sql;
            sql = "SELECT note_id FROM xiaomi_note_id";
            ResultSet rs = stmt.executeQuery(sql);
            // 展开结果集数据库
            while(rs.next()){
                // 通过字段检索
                String id  = rs.getString("note_id");

                xiaomiNoteIds.add(id);
            }
            rs.close();
            stmt.close();
            conn.close();

        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
        return xiaomiNoteIds;
    }

}
